* Potholes, 311 Reports, and a theory of heterogenous resident demand for city services
* Cook, Zuhlke, Saywitz

* this file replicates the maps in the main paper and appendix of Cook, Zuhlke, and Saywitz (2024) PSJ paper. 
* note, the results and figures in the main paper and Appendix are produced in a separate .do file: 
***analysis - full - final - replication materials - 2142022.do

* last updated: February 14, 2024
* last updated by: Sam 

* files needed: 
*** PSJ_RR_Data_10252023.csv
*** houston_tracts.dta (shapefile of houston census tracts)


clear all 

cd "C:\Users\szuhlke\OneDrive - University of Iowa\Desktop\Stata Workspace\potholes"
pwd 

* import delimited "dataforstata.csv", clear /// doesn't account for dropped cts (e.g., airport, roads < 100 m)

 import delimited "PSJ_RR_Data_10252023.csv", clear
save data.dta, replace

* format for figures 
set scheme s1mono

*********************
* save data for each year 


use data.dta, clear
keep if year==16
tostring tract, format(%7.2f) force replace
destring tract, replace
save "HoustonPotholesData-2016.dta", replace

use data.dta, clear
keep if year==17
tostring tract, format(%7.2f) force replace
destring tract, replace
save "HoustonPotholesData-2017.dta", replace

use data.dta, clear
keep if year==18
tostring tract, format(%7.2f) force replace
destring tract, replace
save "HoustonPotholesData-2018.dta", replace

use data.dta, clear
keep if year==19
tostring tract, format(%7.2f) force replace
destring tract, replace
save "HoustonPotholesData-2019.dta", replace

use data.dta, clear
keep if year==20
tostring tract, format(%7.2f) force replace
destring tract, replace
save "HoustonPotholesData-2020.dta", replace

* all have 731 observations 

* merge with shapefile created using:
* spshape2dta "C:\Users\szuhlke\OneDrive - University of Iowa\Desktop\Stata Workspace\potholes\houston_tracts\houston_tracts.shp", replace

use houston_tracts.dta, clear
destring NAME, gen(tract)
merge 1:1 tract using HoustonPotholesData-2016.dta
keep if _merge==3
save "HoustonPotholesData-2016-mapping.dta", replace

use houston_tracts.dta, clear
destring NAME, gen(tract)
merge 1:1 tract using HoustonPotholesData-2017.dta
keep if _merge==3
save "HoustonPotholesData-2017-mapping.dta", replace

use houston_tracts.dta, clear
destring NAME, gen(tract)
merge 1:1 tract using HoustonPotholesData-2018.dta
keep if _merge==3
save "HoustonPotholesData-2018-mapping.dta", replace

use houston_tracts.dta, clear
destring NAME, gen(tract)
merge 1:1 tract using HoustonPotholesData-2019.dta
keep if _merge==3
save "HoustonPotholesData-2019-mapping.dta", replace

use houston_tracts.dta, clear
destring NAME, gen(tract)
merge 1:1 tract using HoustonPotholesData-2020.dta
keep if _merge==3
save "HoustonPotholesData-2020-mapping.dta", replace


**********************
* maps of each variable for appendix 

* 2016
use HoustonPotholesData-2016-mapping.dta, clear
grmap x311_call_count_sum, clnumber(9) fcolor(Greys)
graph export "311calls-2016.jpg", replace

grmap proactive_num_filled_sum, clnumber(9) fcolor(Greys)  // might use this mapping style instead
graph export "potholes-2016.jpg", replace


*2017 
use HoustonPotholesData-2017-mapping.dta, clear
grmap x311_call_count_sum, clnumber(9) fcolor(Greys)
graph export "311calls-2017.jpg", replace

grmap proactive_num_filled_sum, clnumber(9) fcolor(Greys)
graph export "potholes-2017.jpg", replace


*2018
use HoustonPotholesData-2018-mapping.dta, clear
grmap x311_call_count_sum, clnumber(9) fcolor(Greys)
graph export "311calls-2018.jpg", replace

grmap proactive_num_filled_sum, clnumber(9) fcolor(Greys)
graph export "potholes-2018.jpg", replace


*2019
use HoustonPotholesData-2019-mapping.dta, clear
grmap x311_call_count_sum, clnumber(9) fcolor(Greys)
graph export "311calls-2019.jpg", replace

grmap proactive_num_filled_sum, clnumber(9) fcolor(Greys)
graph export "potholes-2019.jpg", replace


*2020
use HoustonPotholesData-2020-mapping.dta, clear
grmap x311_call_count_sum, clnumber(9) fcolor(Greys)
graph export "311calls-2020.jpg", replace

grmap proactive_num_filled_sum, clnumber(9) fcolor(Greys)
graph export "potholes-2020.jpg", replace




* aggregate across time - Figure 4 in main text 
use data.dta, clear

* summarize by tract 
* drop v1 x 
bysort tract: egen ag_x311_total = sum(x311_call_count_sum)
bysort tract: egen ag_proactive_total = sum(proactive_num_filled_sum)
bysort tract: egen ag_black_mean = mean(per_black_mean)
bysort tract: egen ag_hisp_mean = mean(per_hisp_mean)
bysort tract: egen ag_white_mean = mean(per_white_mean)
bysort tract: egen ag_pop_mean = mean(pop_tot_mean)
bysort tract: egen ag_ses_r = mean(ses_r)
bysort tract: egen ag_pop_dens = mean(pop_density)
bysort tract: egen ag_per_asian = mean(per_asian_mean)

keep tract road_length_mean district ag_*
duplicates report
duplicates drop

tostring tract, format(%7.2f) force replace
destring tract, replace
save "data-agg.dta", replace

* merge with spatial 
use houston_tracts.dta, clear
destring NAME, gen(tract)
merge 1:1 tract using data-agg.dta
keep if _merge==3

* map - total 311 reports about potholes, 2016-2020
grmap ag_x311_total, clnumber(9) fcolor(Greys)
graph export "311calls-total.jpg", replace

